﻿CREATE PROCEDURE [RestartFramework].[BeginOrResumeETLJob]
		@pETLJobName		SYSNAME
,		@pServerExecutionId	BIGINT
,		@pETLJobHistoryId	INT		OUTPUT
AS
BEGIN
		SET NOCOUNT ON;
		DECLARE	@vETLJobId					SMALLINT	;
		DECLARE	@vCompleteJobStagesTally	INT			;
		DECLARE	@vETLJobHistoryId			INT			;

		SET	@vETLJobHistoryId = NULL;

		SELECT	@vETLJobId = ej.[ETLJobId]
		FROM	[RestartFramework].[ETLJob] ej
		WHERE	ej.[ETLJobName] = @pETLJobName;

		IF	(@vETLJobId IS NULL)
			RAISERROR('%s is an unknown ETLJobName',16,1,@pETLJobName);
		ELSE
		BEGIN
				SELECT	@vCompleteJobStagesTally = COUNT(*)
				FROM	[RestartFramework].[ETLJobStage] ejs
				WHERE	ejs.[ETLJobId]				=	@vETLJobId
					AND	ejs.[ETLJobStageIsComplete]	=	1;

				IF	@vCompleteJobStagesTally = 0
				BEGIN
						SET	@vETLJobHistoryId = NEXT VALUE FOR [RestartFramework].[ETLJobHistoryId];
						/*Previous execution does not need restarting so Begin a new ETLJobHistoryid*/
						INSERT	[RestartFramework].[ETLJobHistory]([ETLJobHistoryId],[ETLJobId],[ETLJobHistoryStartTime],[RestartTally],[StartedByServerExecutionId])
						VALUES	(@vETLJobHistoryId,@vETLJobId,SYSDATETIMEOFFSET(),0,@pServerExecutionId);
						SET	@pETLJobHistoryId = @vETLJobHistoryId;
				END
				ELSE
				BEGIN
						/*Resume*/
						SELECT	@vETLJobHistoryId = MAX(ejs.[ETLJobHistoryId])
						FROM	[RestartFramework].[ETLJobHistory] ejs
						WHERE	ejs.[ETLJobId] = @vETLJobId;

						UPDATE	[RestartFramework].[ETLJobHistory]
						SET		[RestartTally] = [RestartTally] + 1
						WHERE	[ETLJobHistoryId]	=	@vETLJobHistoryId;
				END
		END
		SET	@pETLJobHistoryId = @vETLJobHistoryId;
END